package com.sptci.rwt;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.sptci.util.CloseJDBCResources;

/**
 * A utility class to execute SQL statements (single or batch).
 *
 * <p>&copy; Copyright 2007 Sans Pareil Technologies, Inc.</p>
 * @author Rakesh Vidyadharan 2007-10-01
 * @version $Id: BatchQueryExecutor.java 20 2007-11-10 00:40:51Z rakesh.vidyadharan $
 */
public class BatchQueryExecutor extends AbstractQueryExecutor
{
  /** An array database names that need special handling. */
  public static final String[] SPECIAL = { "oracle", "mysql" };

  /**
   * Create a new instance using the specified manager to fetch connections.
   *
   * @param manager The connection manager to use.
   */
  public BatchQueryExecutor( final ConnectionManager manager )
  {
    super( manager );
  }

  /**
   * Execute the specified statement(s) and return all the {@link
   * java.sql.ResultSet} and {@link java.sql.Statement#getUpdateCount} 
   * values that result from executing the statement(s).
   *
   * <p><b>Note:</b> Statement processing is handled differently for
   * different database engines.  PostgreSQL and Sybase (probably MS
   * SQL Server) automatically support execution of a batch of statements
   * and return multiple result sets and update counts corresponding to
   * each statement included in the batch.  Oracle and MySQL do not
   * support this feature.  For databases that do not support automatic
   * batch execution, the SQL statement is parsed using a very simple rule -
   * a semi-colon is treated as the delimiter for a statement; and executed
   * individually.  The Oracle default <i>/</i> character is used to denote
   * the separator between individual statements.  Needless to say this is
   * a very serious shortcoming.  You will not be able to execute complex
   * statements such as procedure creation etc for these engines.  The
   * following shows simple syntax for delimited queries.</p>
   *
   * <pre>
   *   select * from table1 order by column1;
   *   /
   *   select * from table2;
   * </pre>
   *
   * @see #parseStatements
   * @see #execute( String, List, Connection, int )
   * @param sql The statement(s) to be executed.
   * @param maxRows The maximum number of rows to fetch for any statement.
   * @return A collection of {@link Rows} objects that represents all
   *   the {@link java.sql.ResultSet} and {@link
   *   java.sql.Statement#getUpdateCount}s generated by executing the
   *   statement(s).
   * @throws SQLException If errors are encountered while executing the
   *   statement(s).
   */
  public List<Rows> execute( final String sql, final int... maxRows )
    throws SQLException
  {
    Connection connection = null;
    List<Rows> collection = new ArrayList<Rows>();
    
    try
    {
      int max = 0;
      if ( maxRows.length > 0 ) max = maxRows[0];

      connection = manager.open();

      DatabaseMetaData dmd = connection.getMetaData();
      final String name = dmd.getDatabaseProductName().toLowerCase();
      boolean special = false;
      for ( String db : SPECIAL )
      {
        if ( name.contains( db ) )
        {
          special = true;
          break;
        }
      }

      if ( special )
      {
        for ( String query : parseStatements( sql ) )
        {
          execute( query, collection, connection, max );
        }
      }
      else
      {
        execute( sql, collection, connection, max );
      }
    }
    finally
    {
      CloseJDBCResources.close( connection );
    }

    return collection;
  }

  /**
   * Execute the specified statement(s) and return a Excel workbook
   * that represents all the data contained in the {@link
   * java.sql.ResultSet}s obtained by executing the statement.
   *
   * @see #execute
   * @param sql The statement to be executed.
   * @return The excel workbook that contains the result set.
   * @throws SQLException If errors are encountered while executing the
   *   statement.
   */
  public HSSFWorkbook export( final String sql ) throws SQLException
  {
    List<Rows> rows = execute( sql );
    ExcelGenerator generator = new ExcelGenerator();
    return generator.generate( sql, rows );
  }

  /**
   * Execute the specified SQL statement and fetch its associated
   * result set or update count.  Fetches multiple result set or update
   * counts if the statement produced multiple results.
   *
   * @since Version 1.1
   * @see #createStatement
   * @see #processResultSet
   * @see #processUpdateCount
   * @param sql The SQL statement to execute.
   * @param list The list to which the results are to be added.
   * @param connection The database connection to use.
   * @param maxRows The maximum number of rows to fetch.
   * @throws SQLException If errors are encountered while executing the query.
   */
  private void execute( final String sql, final List<Rows> list,
      final Connection connection, final int maxRows ) throws SQLException
  {
    Statement statement = null;

    try
    {
      statement = createStatement( connection );
      statement.setMaxRows( maxRows );

      boolean status = statement.execute( sql );

      if ( status )
      {
        list.add( processResultSet( statement, 0, Integer.MAX_VALUE ) );
      }
      else
      {
        list.add( processUpdateCount( statement ) );
      }

      boolean hasMore = false;
      int updateCount = -1;
      while ( ( hasMore = statement.getMoreResults() ) ||
        ( ( updateCount = statement.getUpdateCount() ) != -1 ) )
      {
        list.add( ( ( hasMore ) ? 
            processResultSet( statement, 0, Integer.MAX_VALUE ) :
            processUpdateCount( statement ) ) );

        hasMore = false;
        updateCount = -1;
      }
    }
    finally
    {
      CloseJDBCResources.close( statement );
    }
  }

  /**
   * Parse the specified SQL statement batch and extract individual
   * statements from the batch.  Uses very simple parsing rules, so only
   * ver basic statements can be parsed by this method.  This method may
   * be enhanced over the life-time of the application with more robust
   * parsing rules.
   *
   * <p><b>Note</b> This method currently supports Oracle style <i>/</i> 
   * separated commands.  It is expected that MySQL users follow the same
   * rule.</p>
   *
   * <p>The parser code is taken from a post in the PostgreSQL forum.  The
   * original post can be viewed
   * <a href='http://archives.postgresql.org/pgsql-jdbc/2006-02/msg00034.php'>here</a>.</p>
   *
   * @since Version 1.1
   * @param sql The batch of statements to be parsed.
   * @return The collection of individual statements to be executed.
   */
  private List<String> parseStatements( final String sql )
  {
    List<String> list = new ArrayList<String>();
    StringBuilder builder = new StringBuilder( 128 );
    String query = sql.replaceAll( "\\r\\n", "\\n" );
    String[] queries = query.split( "\\n" );

    boolean statementReady = false;
    int count = 0;
    for ( String line : queries )
    {
      // different continuation for oracle and postgres
      line = line.trim();
      if ( "--/exe/--".equals( line ) ) //execute finished statement for postgres
      {
        builder.append( ' ' );
        statementReady = true;
      }
      else if ( "/".equals( line ) ) //execute finished statement for oracle
      {
        builder.append( ' ' );
        statementReady = true;
      }
      else if ( "go".equalsIgnoreCase( line ) ) //execute finished statement for sybase and potentially MS SQL Server
      {
        builder.append( ' ' );
        statementReady = true;
      }
      else if ( line.startsWith( "--" ) || line.length() == 0 ) // comment or empty
      {
        continue;
      }
      else if ( line.endsWith( ";" ) )
      {
        builder.append( ' ' );
        builder.append( line.substring( 0, line.length() - 1 ) );
        statementReady = true;
      }
      else
      {
        builder.append( ' ' );
        builder.append( line );
        statementReady = false;
      }

      if (statementReady)
      {
        if ( builder.length() < 2 ) continue;
        list.add( builder.toString() );
        ++count;
        builder = new StringBuilder( 128 );
      }
    }

    return list;
  }
}
